
set matsize 1500
clear

use "wave13-16_FULL.dta"
keep if swave==13

*****Merge with license
merge m:1 lgtkey using "Module.dta", generate(_licensemerge)


merge m:1 lgtkey using "Wave2_topical", generate(edumerge) keepusing(ecourse1 ecourse2 ecourse3)
drop if edumerge==2
gen math=0
replace math=1 if ecourse1==1
gen sci=0
replace sci=1 if ecourse2==1
gen eng=0
replace eng=1 if ecourse3==1
********************************Data Management***********************
drop if tage<18 
drop if tage >64 

* Work hour<1
drop if ejbhrs1<1


*delege useless license data
drop if  iprocert <1

*Delete imptued license 
drop if iwhopcer <0 

* Define demographic
generate female =1
replace female =0 if esex==1
drop if female==1

generate black=0
replace black=1 if erace==2

generate hispanic=0
replace hispanic=1 if eorigin==1 & black==0

*Create hourly wage
generate long wage = (tpmsum1)/4/ejbhrs1
drop if wage>100
drop if wage<5
gen lwage =log(wage)
generate union=1
replace union=0 if eunion1==2

*Delete imputed union data
drop if eunion1 <0 

*Drop race other than white and black
gen other=0
replace other=1 if erace>2

***Merging with Module data of license (skip)
***Create license dummy (skip)

*Delete imputed licensure source
drop if iwhopcer <0 


*Create gov't summy
generate govt=0
replace govt=1 if inrange(eclwrk1, 3,5)

*service worker?
gen service=0
replace service=1 if inrange(ejbind1,6870,9290)

*Create education dummy
generate hs =0
replace hs=1 if eeducate==39
generate somecollege =0
replace somecollege=1 if inrange(eeducate,40,43)
generate college=0
replace college=1 if eeducate ==44
generate postgrad=0
replace postgrad=1 if eeducate >44

*Create self-employment and big company dummy
generate self_emp=1
replace self_emp=0 if ebuscntr<0

gen age2=tage*tage
rename tage age


*Ensure that the license variable does not contain certified
replace license=0 if cert==1

*Ensure the license indicator include only license being required by job
gen licensenorequire=0
replace licensenorequire=1 if irjpcert!=1 & license==1
replace license=0 if irjpcert>1


gen counting=1
egen sizecheck=total(counting), by(tfipsst tjbocc1)

***** Correction 1 (hybrid)
************** Redefine licensing by 50/50 rule
egen licensecheck=mean(license), by(tfipsst tjbocc1)
replace license=0 if licensecheck<0.5 & sizecheck>4
replace license=1 if licensecheck>=0.5 & sizecheck>4


		*Merge with ban data
	gen state_occ=string(tfipsst)+"-"+string(tjbocc1)

	merge m:1 state_occ using "mandatory_unique.dta", generate(_felonymerge)

	drop if _felonymerge==2
	replace permban=0 if _felonymerge==1
	replace felony=0 if _felonymerge==1
	
	*** Correction 2 (admin data override)
	replace license=1 if felony==1
	
	rename permban Ban
	label var Ban "Ban"
	
	***"permban" only contains permanent ban
	***"felony" contains both temporary and permanent ban

	****Correction 3 (occ misrreport)
	gen change=0
replace change=1 if license==0 & licensecheck>=0.5
replace change=1 if license==1 & licensecheck<0.5


gen special1=0
replace special1=1 if change==1 & inrange(tjbocc1,10,440)
gen special2=0
replace special2=1 if change==1 & inrange(tjbocc1,500,960)
gen special3=0
replace special3=1 if change==1 & inrange(tjbocc1,1000,1240)
gen special4=0
replace special4=1 if change==1 & inrange(tjbocc1,1300,1560)
gen special5=0
replace special5=1 if change==1 & inrange(tjbocc1,1600,1980)
gen special6=0
replace special6=1 if change==1 & inrange(tjbocc1,2000,2060)
gen special7=0
replace special7=1 if change==1 & inrange(tjbocc1,2100,2180)
gen special8=0
replace special8=1 if change==1 & inrange(tjbocc1,2200,2555)
gen special9=0
replace special9=1 if change==1 & inrange(tjbocc1,2600,2970)
gen special10=0
replace special10=1 if change==1 & inrange(tjbocc1,3000,3550)
gen special11=0
replace special11=1 if change==1 & inrange(tjbocc1,3600,3655)
gen special12=0
replace special12=1 if change==1 & inrange(tjbocc1,3700,3960)
gen special13=0
replace special13=1 if change==1 & inrange(tjbocc1,4000,4160)
gen special14=0
replace special14=1 if change==1 & inrange(tjbocc1,4200,4255)
gen special15=0
replace special15=1 if change==1 & inrange(tjbocc1,4330,4655)
gen special16=0
replace special16=1 if change==1 & inrange(tjbocc1,4700,4965)
gen special17=0
replace special17=1 if change==1 & inrange(tjbocc1,5000,5940)
gen special18=0
replace special18=1 if change==1 & inrange(tjbocc1,6000,6950)
gen special19=0
replace special19=1 if change==1 & inrange(tjbocc1,7000,7640)
gen special20=0
replace special20=1 if change==1 & inrange(tjbocc1,7700,8990)
gen special21=0
replace special21=1 if change==1 & tjbocc1>8990

	foreach y in license Ban{
		gen `y'_black=`y'*black
		label var `y'_black "`y'*black"
	}

	keep if apmsum1==0 & inrange(wage,5,100)


	gen person=0
	replace person=1 if iwhypcer==2

	***************** Race selection
	drop if other==1
	drop if hispanic==1 & black==0

	***********************************
	**** Main Table
	***********************************
	replace quality=100 if missing(quality)

	local controls  age age2  hs somecollege college postgrad union govt self_em service 

	

	gen boxyes=0
	replace boxyes=1 if inlist(tfipsst,6,8,9,11,15,25,27,35)

	gen BTBban=Ban*boxyes
	replace Ban=0 if BTBban==1
	gen nonBTBban=Ban

	foreach y in BTBban nonBTBban{
		gen `y'_black=`y'*black
		label var `y'_black "`y'*black"
	}


	regress lwage black  license license_*  BTBban* nonBTBban* cert licensenorequire special* `controls'  i.tfipsst i.tjbocc1 i.rhcalmn if apmsum1==0 & inrange(wage,5,100) [pweight=wpfinwgt], vce(cluster tfipsst)
	outreg2 using  Table6.tex, keep(black license license_* BTBban* nonBTBban*) label ctitle("All")

	drop if inlist(tjbocc1,1300,3140,4500,9120,3000,3310,3010,9130,3400,4465,4510,4810,2100,3500,3150,3040,4240,3050,3160,3110,3060,3120,4920,3255,3220,9140,2300,2310,2320,3250,8620)
	regress lwage black  license license_*  BTBban* nonBTBban* cert licensenorequire special* `controls'  i.tfipsst i.tjbocc1 i.rhcalmn if apmsum1==0 & inrange(wage,5,100) [pweight=wpfinwgt], vce(cluster tfipsst)
	outreg2 using  Table6.tex, keep(black license license_* BTBban* nonBTBban*) label ctitle("All")
